﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using EmoneyWeiXinCommon;
using System.Data;

namespace EmoneyWeiXinDAL
{
    public class QQKeysNewDAL
    {

        /// <summary>
        /// 添加QQ群Key
        /// </summary>
        /// <param name="name"></param>
        /// <param name="typeid"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static int InsertQQKeysNew(string name, string number, int typeid, string key,string createUser)
        {
            string sql = "insert into QQKeysNew values(@QQname,@QQkey,@number,@typeId,0,GETDATE(),@createUser)";
            SqlParameter[] paras = 
            {
              new SqlParameter("@QQname",name),
              new SqlParameter("@QQkey",key),
              new SqlParameter("@typeId",typeid),
              new SqlParameter("@number",number),
              new SqlParameter("@createUser",createUser)
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        /// <summary>
        /// 添加QQ群类型
        /// </summary>
        /// <param name="QQKeyTypeName"></param>
        /// <returns></returns>
        public static int InsertQQKeyTypes(string QQKeyTypeName,string typePortName, string createUser)
        {
            string sql = "Insert Into QQKeyTypes values(@typeName,@createUser,GETDATE(),@typePortName)";
            SqlParameter[] paras = 
            {
              new SqlParameter("@typeName",QQKeyTypeName),
              new SqlParameter("@createUser",createUser),
              new SqlParameter("@typePortName",typePortName),
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        /// <summary>
        /// 删除QQ群key
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static int DeleteQQKey(string Id)
        {
            string sql = "DELETE QQKeysNew where Id=@Id";
            SqlParameter[] paras =
            {
              new SqlParameter("@Id",Id)
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        /// <summary>
        /// 根据类型获取集合
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public static DataTable GetQQkeysByTypeId(string typeId, string name,string createUser)
        {
            string sql = " SELECT QQKeysNew.*,QQKeyTypes.TypeName  FROM QQKeysNew INNER JOIN QQKeyTypes ON QQKeysNew.QQTypeId=QQKeyTypes.Id WHERE QQKeyTypes.createUser=@CreateUser ";
            if (typeId != "-1")
            {
                sql += "AND QQTypeId=@typeId ";
            }

            if (name != "全部")
            {
                sql += " And QQName=@QQName";
            }

            sql += " order by CreateTime DESC,statu";
            SqlParameter[] paras = 
            {
              new SqlParameter("@typeId",typeId),
              new SqlParameter("@QQName",name),
              new SqlParameter("@CreateUser",createUser)
            };
            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }


        public static DataTable GetUsingQQkeys()
        {
            string sql = "SELECT * FROM QQKeysNew WHERE statu=1";

            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, null);
        }

        public static int DeleteType(string keyTypeId)
        {
            string sql = "DELETE QQKeyTypes where Id=@Id";
            sql += " DELETE QQKeysNew where QQTypeId=@Id";
            SqlParameter[] paras =
          {
            new SqlParameter("@Id",keyTypeId)
          };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        public static DataTable GetAllTypes(string createUser)
        {
            string sql = "SELECT * FROM QQKeyTypes where createUser=@createUser";
            SqlParameter[] paras =
          {
            new SqlParameter("@createUser",createUser)
          };
            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        public static int SetUsing(string qqKeyId, string type)
        {
            string sql = "update QQKeysNew set statu=0 where QQTypeId=@type AND statu=1"; //先将启动的设为非启动
            sql += "update QQKeysNew set statu=1 where QQTypeId=@type and Id=@qqKeyId";
            SqlParameter[] paras = 
            {
              new SqlParameter("@type",type),
              new SqlParameter("@qqKeyId",qqKeyId),
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        public static DataTable checkLogin(string userName, string pwd)
        {
            string sql = "SELECT * FROM QQqunUsers where userName=@userName AND userPwd=@userPwd";
            SqlParameter[] paras = 
            {
               new SqlParameter("@userName",userName),
               new SqlParameter("@userPwd",pwd)
            };
            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }

        public static DataTable GetAllName(string createUser)
        {
            string sql = "select QQName from QQKeysNew where createUser=@createUser group by QQName ";
            SqlParameter[] paras = 
            {
                new SqlParameter("@createUser",createUser)
            };
            return SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, System.Data.CommandType.Text, sql, paras);
        }
    }
}
